﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_XuatkhoDAL : ClsConnection
    {
        static string sNamhocId;
        public ClsTblDm_XuatkhoDAL()
        {
            sNamhocId = ClsTblDm_Namhocdal.ReturnNamhocId().ToString();
        }
        public DataTable GetTable_GridDanhsachbaoan(DateTime values, int NamhocId)
        {

            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLDANHSACHBAOAN.Replace("@Ngay", "#" + values.ToShortDateString() + "#").Replace("@NamhocId", NamhocId.ToString()), cn);
            DataTable table = new DataTable();
            try
            {

                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        //
        public DataTable GetTable_TblXuathanghoa_GetbyNgayNX(DateTime values)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLXUATHANGHOA_GETBYNGAYNX.Replace("@NgayNX", "#" + values.ToShortDateString() + "#"), cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            // table.Columns.Add("Ngay",table.Columns["NgayNX"].DataType);
            table.Columns["NgayNX"].ColumnName = "Ngay";
            return table;
        }
        public DataTable GetTable_GridDanhsachnguyenlieudukienmua(DateTime values)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLDANHSACHNGUYENLIEUDUKIENMUA.Replace("@Ngay", "#" + values.ToShortDateString() + "#"), cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {

                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        public DataTable GetTbl_TblNguyenlieutonkhovagia(int Thucpham_ThucanId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("prc_TblNguyenlieutonkhovagia", cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.Parameters.Add("@Thucpham_ThucanId", OleDbType.Integer).Value = Thucpham_ThucanId;
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        // 
        public DataTable TonghophoadondichoByNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TonghophoadondichoByNgay", cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        public DataTable TblXuathanghoagiatrungbinh_GetbyNgayNX(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TblXuathanghoagiatrungbinh_GetbyNgayNX", cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }

        public DataTable GetTbl_TblNguyenlieutonkho()
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string StrSql = "SELECT     Round(SUM(Soluong),10) AS Soluong, Thucpham_ThucanId FROM         (SELECT     Dongia, Soluong, Thucpham_ThucanId                         FROM          View_Nhaphanghoa                         UNION all                         SELECT     Dongia, Soluong, Thucpham_ThucanId                         FROM         View_Xuathanghoa) AS TblNhapxuathanghoa  GROUP BY Thucpham_ThucanId  HAVING      (SUM(Soluong) <> 0) ";
            OleDbCommand commad = new OleDbCommand(StrSql, cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        ClsTblDm_NamhocDAL ClsTblDm_Namhocdal = new ClsTblDm_NamhocDAL();
        public void TblHoadonnhapxuat_Add(DateTime NgayNX, float Tongtien)
        {
            string NamePrc = " INSERT INTO TblHoadonnhapxuat (NgayNX, Tongtien, Trangthai, NamhocId ) VALUES ( #" + NgayNX.ToShortDateString() + "#, " + Tongtien.ToString() + ", 0, " + ClsTblDm_Namhocdal.ReturnNamhocId() + " )";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        //
        public DataTable GetDonvitinhIdById(int Thucpham_thucanId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TblThucpham_thucan_GetDonvitinhIdById", cn);
            commad.Parameters.Add(new OleDbParameter("@Id", Thucpham_thucanId));
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        public DataTable TblHoadonnhap_GetId_ByLonhonNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT"
   + " 	Id, NgayNX, Tongtien, Trangthai "
   + " FROM TblHoadonnhapxuat "
   + " where Trangthai =-1 and NgayNX >= #" + NgayNX.ToShortDateString() + "# "
   + " ORDER BY NgayNX DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return Set.Tables[0];

        }
        public DataTable TblHoadonnhapxuat_GetId_ByLonhonNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT"
   + " 	Id, NgayNX, Tongtien, Trangthai "
   + " FROM TblHoadonnhapxuat "
   + " where Trangthai = 0 and NgayNX >= #" + NgayNX.ToShortDateString() + "# "
   + " ORDER BY NgayNX DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return Set.Tables[0];

        }
        public DataTable TblHoadonnhapxuatReturnDSxuatthucpham_PLG_ByNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT TblNhapxuathanghoa.Thucpham_ThucanId, TblNhapxuathanghoa.DonvitinhId, TblThucpham_thucan.Tenthucan, (select Tendonvitinh from TblDm_Donvitinh where Id = TblNhapxuathanghoa.DonvitinhId) AS Tendonvitinh, IIf(IsNull(TblThucpham_thucan.Calo),0,TblThucpham_thucan.Calo) AS Kcalo, IIf(Nguondongvat,Tphh_Protid,0) AS Protid_DV, IIf(Nguondongvat,Tphh_Lipid,0) AS Lipid_DV, IIf(Nguondongvat,0,Tphh_Protid) AS Protid_TV, IIf(Nguondongvat,0,Tphh_Lipid) AS Lipid_TV, TblThanhphanhoahoc.Tphh_GlucId AS GlucId, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Tylethai, TblNhapxuathanghoa.Soluongcon AS Soluongton, TblNhapxuathanghoa.Soluong AS Soluongxuat, TblNhapxuathanghoa.Dongia,  " +
   "  (TblNhapxuathanghoa.Soluong *  TblNhapxuathanghoa.Dongia) AS Thanhtien, " +
   " TblHoadonnhapxuat.NgayNX " +
  "  FROM TblThanhphanhoahoc INNER JOIN (TblThucpham_thucan INNER JOIN (TblNhapxuathanghoa INNER JOIN TblHoadonnhapxuat ON TblNhapxuathanghoa.HoadonnhapxuatId = TblHoadonnhapxuat.Id) ON TblThucpham_thucan.Id = TblNhapxuathanghoa.Thucpham_ThucanId) ON TblThanhphanhoahoc.IdThucpham_thucan = TblThucpham_thucan.Id " +
  "  WHERE (((TblHoadonnhapxuat.NgayNX)=#" + NgayNX + "#) AND TblHoadonnhapxuat.NamhocId=(select Id from TblDm_namhoc where Trangthai =-1) and  ((TblHoadonnhapxuat.Trangthai)=0)) " +
   " ORDER BY TblHoadonnhapxuat.NgayNX DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return Set.Tables[0];

        }
        public DataTable TblThucphamnhapById(int IdThucpham_thucan)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string SPROC_TBLHOADONNHAPXUAT_GET = " SELECT  ROUND(View_Tonkho.SoLuong,10) AS Soluong,(select top 1 View_Nhaphanghoa.Dongia from View_Nhaphanghoa where View_Nhaphanghoa.Thucpham_ThucanId = View_Tonkho.Thucpham_ThucanId Order by  View_Nhaphanghoa.Id DESC ) AS Dongia  From View_Tonkho    where  View_Tonkho.Thucpham_ThucanId =" + IdThucpham_thucan.ToString();

            OleDbCommand commad = new OleDbCommand(SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            {
            }
            return Set.Tables[0];
        }

        public DataTable NewTblHoadonnhapxuatReturnDSxuatthucpham_PLG_ByNgay(DateTime NgayNX, DataTable TblDm_Khoilop)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string SPROC_TBLHOADONNHAPXUAT_GET = " SELECT Thucpham_ThucanId, DonvitinhId,Tendonvitinh, Tenthucan,Kcalo,Protid_DV, Lipid_DV,Protid_TV,Lipid_TV,GlucId, Tylequydoi,Tylethai, ROUND(Soluongton,10) AS Soluongton, ROUND(SUM(Soluongxuat),10) AS Soluongxuat, Dongia ,Thanhtien, NgayNX From (";
            for (int i = 0; i < TblDm_Khoilop.Rows.Count; i++)
            {
                if (i == 0)
                {
                    SPROC_TBLHOADONNHAPXUAT_GET = SPROC_TBLHOADONNHAPXUAT_GET + " SELECT Thucphamtheobua.Thucpham_ThucanId, TblThucpham_thucan.DonvitinhId, (select Tendonvitinh from TblDm_Donvitinh  where Id =TblThucpham_thucan.DonvitinhId ) AS Tendonvitinh, TblThucpham_thucan.Tenthucan, IIf(IsNull(TblThucpham_thucan.Calo),0,TblThucpham_thucan.Calo) AS Kcalo, IIf(Nguondongvat,Tphh_Protid,0) AS Protid_DV, IIf(Nguondongvat,Tphh_Lipid,0) AS Lipid_DV, IIf(Nguondongvat,0,Tphh_Protid) AS Protid_TV, IIf(Nguondongvat,0,Tphh_Lipid) AS Lipid_TV, TblThanhphanhoahoc.Tphh_GlucId AS GlucId, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Tylethai,  IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01)AS Soluongton, Thucphamtheobua.Soluong AS Soluongxuat, IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01) AS Dongia, IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01) AS Thanhtien, Thucphamtheobua.Ngay AS NgayNX " +
           " FROM Thucphamtheobua INNER JOIN (TblThanhphanhoahoc INNER JOIN TblThucpham_thucan ON TblThanhphanhoahoc.IdThucpham_thucan = TblThucpham_thucan.Id) ON Thucphamtheobua.Thucpham_ThucanId = TblThucpham_thucan.Id " +
           " WHERE Thucphamtheobua.Ngay=#" + NgayNX + "# AND Thucphamtheobua.NamhocId=" + sNamhocId + " AND Thucphamtheobua.Khoilop=" + TblDm_Khoilop.Rows[i]["Id"].ToString() + " UNION ALL ";

                }
                else
                {
                    if (i == TblDm_Khoilop.Rows.Count - 1)
                    {
                        SPROC_TBLHOADONNHAPXUAT_GET = SPROC_TBLHOADONNHAPXUAT_GET + " SELECT Thucphamtheobua.Thucpham_ThucanId, TblThucpham_thucan.DonvitinhId, (select Tendonvitinh from TblDm_Donvitinh  where Id =TblThucpham_thucan.DonvitinhId ) AS Tendonvitinh, TblThucpham_thucan.Tenthucan, IIf(IsNull(TblThucpham_thucan.Calo),0,TblThucpham_thucan.Calo) AS Kcalo, IIf(Nguondongvat,Tphh_Protid,0) AS Protid_DV, IIf(Nguondongvat,Tphh_Lipid,0) AS Lipid_DV, IIf(Nguondongvat,0,Tphh_Protid) AS Protid_TV, IIf(Nguondongvat,0,Tphh_Lipid) AS Lipid_TV, TblThanhphanhoahoc.Tphh_GlucId AS GlucId, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Tylethai,IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01)AS Soluongton, Thucphamtheobua.Soluong AS Soluongxuat, IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01) AS Dongia, IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01) AS Thanhtien, Thucphamtheobua.Ngay AS NgayNX " +
            " FROM Thucphamtheobua INNER JOIN (TblThanhphanhoahoc INNER JOIN TblThucpham_thucan ON TblThanhphanhoahoc.IdThucpham_thucan = TblThucpham_thucan.Id) ON Thucphamtheobua.Thucpham_ThucanId = TblThucpham_thucan.Id " +
            " WHERE Thucphamtheobua.Ngay=#" + NgayNX + "# AND Thucphamtheobua.NamhocId=" + sNamhocId + " AND Thucphamtheobua.Khoilop=" + TblDm_Khoilop.Rows[i]["Id"].ToString() + " ) AS TblXuatthucphamchotruong GROUP BY Thucpham_ThucanId, DonvitinhId,Tendonvitinh, Tenthucan,Kcalo,Protid_DV, Lipid_DV,Protid_TV,Lipid_TV,GlucId, Tylequydoi,Tylethai,Soluongton,  Dongia,Thanhtien, NgayNX HAVING (SUM(Soluongxuat)<>0) ";

                    }
                    else
                    {
                        SPROC_TBLHOADONNHAPXUAT_GET = SPROC_TBLHOADONNHAPXUAT_GET + " SELECT Thucphamtheobua.Thucpham_ThucanId, TblThucpham_thucan.DonvitinhId, (select Tendonvitinh from TblDm_Donvitinh  where Id =TblThucpham_thucan.DonvitinhId ) AS Tendonvitinh, TblThucpham_thucan.Tenthucan, IIf(IsNull(TblThucpham_thucan.Calo),0,TblThucpham_thucan.Calo) AS Kcalo, IIf(Nguondongvat,Tphh_Protid,0) AS Protid_DV, IIf(Nguondongvat,Tphh_Lipid,0) AS Lipid_DV, IIf(Nguondongvat,0,Tphh_Protid) AS Protid_TV, IIf(Nguondongvat,0,Tphh_Lipid) AS Lipid_TV, TblThanhphanhoahoc.Tphh_GlucId AS GlucId, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Tylethai,IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01)AS Soluongton, Thucphamtheobua.Soluong AS Soluongxuat, IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01) AS Dongia, IIf(IsNull(Thucphamtheobua.Dongiathucte),0*0.01,0*0.01) AS Thanhtien, Thucphamtheobua.Ngay AS NgayNX " +
            " FROM Thucphamtheobua INNER JOIN (TblThanhphanhoahoc INNER JOIN TblThucpham_thucan ON TblThanhphanhoahoc.IdThucpham_thucan = TblThucpham_thucan.Id) ON Thucphamtheobua.Thucpham_ThucanId = TblThucpham_thucan.Id " +
            " WHERE Thucphamtheobua.Ngay=#" + NgayNX + "# AND Thucphamtheobua.NamhocId=" + sNamhocId + " AND Thucphamtheobua.Khoilop=" + TblDm_Khoilop.Rows[i]["Id"].ToString() + "  UNION ALL ";
                    }
                }
            }
            OleDbCommand commad = new OleDbCommand(SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            for (int iVale = 0; iVale < Set.Tables[0].Rows.Count; iVale++)
            {
                DataTable _Tbl = TblThucphamnhapById(int.Parse(Set.Tables[0].Rows[iVale]["Thucpham_thucanId"].ToString()));
                try
                {
                    Set.Tables[0].Rows[iVale]["Soluongton"] = _Tbl.Rows[0]["Soluong"].ToString().Trim();
                    Set.Tables[0].Rows[iVale]["Dongia"] = _Tbl.Rows[0]["Dongia"].ToString().Trim();
                    Set.Tables[0].Rows[iVale]["Thanhtien"] = (float.Parse(Set.Tables[0].Rows[iVale]["Dongia"].ToString()) * float.Parse(Set.Tables[0].Rows[iVale]["Soluongxuat"].ToString())).ToString().Trim();
                }
                catch { }
            }
            return Set.Tables[0];

        }
        public DataTable TblXuatchokhoilop_Ngay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string sql = "SELECT top 1  Thucphamtheobua.Dongiathucte FROM Thucphamtheobua WHERE (((Thucphamtheobua.Ngay)=#" + NgayNX.ToShortDateString() + "#));";
            OleDbCommand commad = new OleDbCommand(sql, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
                Set.Tables[0].Columns["Soluong"].ColumnName = "Soluongxuat";
                //Set.Tables[0].Columns["Soluong"].Caption = "Soluongxuat";
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

            return Set.Tables[0];

        }
        public void UpdatePhatsinhByNgay(string Phatsinh, DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " Update Thucphamtheobua set Dongiathucte = " + Phatsinh.Trim() + "  where Ngay =#" + NgayNX.ToShortDateString() + "# ";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

        }

        public DataTable TblXuatchokhoilop_ByKhoilop_Ngay(DateTime NgayNX, int KhoilopId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string sql = "SELECT Thucphamtheobua.Thucpham_ThucanId, (SELECT TblThucpham_thucan.DonvitinhId FROM TblThucpham_thucan where Id = Thucphamtheobua.Thucpham_ThucanId) AS DonvitinhId, " +
  " (SELECT TblThucpham_thucan.Tenthucan FROM TblThucpham_thucan where id = Thucphamtheobua.Thucpham_ThucanId)  AS Tenthucan " +
  " ,(SELECT TblDm_Donvitinh.Tendonvitinh FROM TblDm_Donvitinh INNER JOIN TblThucpham_thucan ON TblDm_Donvitinh.Id = TblThucpham_thucan.DonvitinhId where TblThucpham_thucan.Id =Thucphamtheobua.Thucpham_ThucanId) AS Tendonvitinh,  Thucphamtheobua.Soluong ,Thucphamtheobua.dongia,(Thucphamtheobua.Soluong * Thucphamtheobua.dongia) AS Thanhtien, Thucphamtheobua.Ngay AS NgayNX " +
   " FROM Thucphamtheobua where Ngay =#" + NgayNX.ToShortDateString() + "# and  Thucphamtheobua.NamhocId=(select Id from TblDm_namhoc where Trangthai =-1) and   Khoilop =" + KhoilopId.ToString() + "";
            OleDbCommand commad = new OleDbCommand(sql, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
                Set.Tables[0].Columns["Soluong"].ColumnName = "Soluongxuat";
                //Set.Tables[0].Columns["Soluong"].Caption = "Soluongxuat";
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

            return Set.Tables[0];

        }
        public DataTable TblHoadonnhap_GetId_ByNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT"
   + " 	Id, NgayNX, Tongtien, Trangthai "
   + " FROM TblHoadonnhapxuat "
   + " where Trangthai = -1 and NgayNX = #" + NgayNX.ToShortDateString() + "# "
   + " ORDER BY Id DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return Set.Tables[0];

        }
        public DataTable TblHoadonnhapxuat_GetId_ByNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT"
   + " 	Id, NgayNX, Tongtien, Trangthai "
   + " FROM TblHoadonnhapxuat "
   + " where Trangthai = 0 and NgayNX = #" + NgayNX.ToShortDateString() + "# "
   + " ORDER BY Id DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return Set.Tables[0];

        }
        public void DeleteHoadon_XuathangbyNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " delete from TblNhapxuathanghoa where HoadonnhapxuatId in (select Id from TblHoadonnhapxuat where Trangthai = 0 and NgayNX = #" + NgayNX.ToShortDateString() + "# " + " ) ";
            string Str2 = "delete FROM TblHoadonnhapxuat  where Trangthai = 0 and NgayNX = #" + NgayNX.ToShortDateString() + "# ";
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = " delete from TblNhapxuathanghoa where HoadonnhapxuatId in (select Id from TblHoadonnhapxuat where Trangthai = 0 and NgayNX = #" + NgayNX.ToShortDateString() + "# " + " ) ; delete FROM TblHoadonnhapxuat  where Trangthai = 0 and NgayNX = #" + NgayNX.ToShortDateString() + "# ";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                commad.CommandText = Str2;
                commad.ExecuteNonQuery();

                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

        }
        public void Delete_All_Hoadon_XuathangAndThucphamxuatbyHoadonId(int HoadonxuatId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " delete from TblNhapxuathanghoa where HoadonnhapxuatId =" + HoadonxuatId.ToString() + "  ";
            string Str2 = "delete FROM TblHoadonnhapxuat  where Id =" + HoadonxuatId.ToString() + " ";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                commad.CommandText = Str2;
                commad.ExecuteNonQuery();

                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

        }
        public void Delete_XuathangbyMahoadon(int Mahoadon)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " delete from TblNhapxuathanghoa where HoadonnhapxuatId in (select Id from TblHoadonnhapxuat where Trangthai = 0 and HoadonnhapxuatId = " + Mahoadon.ToString() + " " + " ) ";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
        }
        public void Insert_XuathangchokhoiBy(DateTime NgayNX, int KhoilopId, int Thucpham_thucanId, string Soluong, string Gia)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " insert into Thucphamtheobua(Khoilop,Ngay, thucpham_thucanId,Soluong,Dongia, NamhocId ) values(" + KhoilopId.ToString() + ",#" + NgayNX.ToShortDateString() + "# ," + Thucpham_thucanId.ToString() + " ," + Soluong.ToString() + "," + Gia.ToString() + "," + sNamhocId + ")";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

        }
        public void Delete_XuathangchokhoiBy(DateTime NgayNX, int KhoilopId, int Thucpham_thucanId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " delete from Thucphamtheobua where Ngay=#" + NgayNX.ToShortDateString() + "# and Khoilop =" + KhoilopId.ToString() + " and Thucpham_thucanId =" + Thucpham_thucanId.ToString() + " and namhocId =" + ClsTblDm_Namhocdal.ReturnNamhocId() + "";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

        }
        public void Delete_ALL_XuathangchokhoiBy(DateTime NgayNX, int KhoilopId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Str1 = " delete from Thucphamtheobua where Ngay=#" + NgayNX.ToShortDateString() + "# and Khoilop =" + KhoilopId.ToString() + "  and namhocId =" + ClsTblDm_Namhocdal.ReturnNamhocId() + "";
            OleDbCommand commad = new OleDbCommand();
            commad.Connection = cn;
            try
            {
                commad.CommandText = Str1;
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }

        }
        public DataSet SetHoadonbyBgDatetimeAndEndDatetime(DateTime BgDate, DateTime EndDate)
        {
            string NamePrc = "SELECT TblHoadonnhapxuat.Id, TblHoadonnhapxuat.NgayNX FROM TblHoadonnhapxuat WHERE (((TblHoadonnhapxuat.NgayNX)<=#" + EndDate.ToShortDateString() + "#) AND ((TblHoadonnhapxuat.Trangthai)=0) AND ((TblHoadonnhapxuat.NgayNX)>=#" + BgDate.ToShortDateString() + "#))";
            string NameTable = "TblDm_To";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();


            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset;
        }

        public DataTable TblHoadonnhapxuat_GetId_ByFindNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT"
   + " 	Id, NgayNX, Tongtien, Trangthai "
   + " FROM TblHoadonnhapxuat "
   + " where Trangthai = 0 and NgayNX = #" + NgayNX.ToShortDateString() + "# "
   + " ORDER BY Id DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            DataSet Set = new DataSet();
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(Set);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return Set.Tables[0];

        }
        public void TblNhapxuathanghoa_Add(int HoadonnhapxuatId, int DonvitinhId, int Thucpham_ThucanId, string Soluong, string Dongia, string Soluongcon)
        {
            string NamePrc = " INSERT INTO TblNhapxuathanghoa (HoadonnhapxuatId,DonvitinhId,Thucpham_ThucanId,Soluong,Dongia,Soluongcon) VALUES( " + HoadonnhapxuatId.ToString() + ", " + DonvitinhId.ToString() + ", " + Thucpham_ThucanId.ToString() + ", " + Soluong.ToString() + ", " + Dongia.ToString() + ", " + Soluongcon.ToString() + ") ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Edit_Hoadondonxuat(int HoadonnhapxuatId, float Tongtien)
        {
            string NamePrc = "Update TblHoadonnhapxuat set Tongtien = " + Tongtien.ToString() + " WHERE Id = " + HoadonnhapxuatId.ToString() + " ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void TblHoadonnhapxuat_DeleByNamhocId(int NamhocId)
        {
            string NamePrc = "delete FROM TblHoadonnhapxuat WHERE NamhocId = " + NamhocId.ToString();
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void TblHoadonnhapxuat_DeleByHoadon(int HoadonnhapxuatId)
        {
            string NamePrc = QueryString.SPROC_TBLHOADONNHAPXUAT_DELEBYHOADON;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@HoadonnhapxuatId", HoadonnhapxuatId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void TblXuathanghoagiatrungbinh_DeleByHoadon(int HoadonnhapxuatId)
        {
            string NamePrc = QueryString.SPROC_TBLXUATHANGHOAGIATRUNGBINH_DELEBYHOADON;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@HoadonnhapxuatId", HoadonnhapxuatId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

        public void TblNhapxuathanghoa_DeleteByhoadon(int HoadonnhapxuatId)
        {
            string NamePrc = QueryString.PRCTBLDM_HOADONXUAT_DELETE;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@HoadonnhapxuatId", HoadonnhapxuatId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void TblXuathanghoagiatrungbinh_Add(int HoadonnhapxuatId, int DonvitinhId, int Thucpham_ThucanId, float Dongiatrungbinh,
            float Soluong, float Soluongcon, float Thanhtien)
        {

            string NamePrc = QueryString.SPROC_TBLXUATHANGHOAGIATRUNGBINH_ADD;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@HoadonnhapxuatId", HoadonnhapxuatId));
            Cmm.Parameters.Add(new OleDbParameter("@DonvitinhId", DonvitinhId));
            Cmm.Parameters.Add(new OleDbParameter("@Thucpham_ThucanId", Thucpham_ThucanId));
            Cmm.Parameters.Add(new OleDbParameter("@Dongia", Dongiatrungbinh.ToString()));
            Cmm.Parameters.Add(new OleDbParameter("@Soluong", Soluong));
            Cmm.Parameters.Add(new OleDbParameter("@Soluongcon", Soluongcon.ToString()));
            Cmm.Parameters.Add(new OleDbParameter("@Thanhtien", Thanhtien.ToString()));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void TblTemp_Add(int DonvitinhId, int Thucpham_ThucanId, float Soluonglayxuat, float Soluongcon, float Dongia)
        {
            string NamePrc = QueryString.SPROC_TBLTEMP_ADD;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@DonvitinhId", DonvitinhId));
            Cmm.Parameters.Add(new OleDbParameter("@Thucpham_ThucanId", Thucpham_ThucanId));
            Cmm.Parameters.Add(new OleDbParameter("@Soluonglayxuat", Soluonglayxuat.ToString()));
            Cmm.Parameters.Add(new OleDbParameter("@Soluongcon", Soluongcon.ToString()));
            Cmm.Parameters.Add(new OleDbParameter("@Dongia", Dongia.ToString()));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataTable TblChitiethoadonnhapxuat_ByNgay(DateTime NgayNX, int Thucpham_thucanId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("proc_TblChitiethoadonnhapxuat", cn);
            commad.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));
            commad.Parameters.Add(new OleDbParameter("@Thucpham_thucanId", Thucpham_thucanId));
            DataTable table = new DataTable();
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        } 
        public DataTable TblTemp_Get()
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TblTemp_Get", cn);

            DataTable table = new DataTable();
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        public DataTable TblTemp_GetbyThucpham_thucanId(int Thucpham_thucanId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TblTemp_GetbyThucpham_thucanId", cn);
            commad.Parameters.Add(new OleDbParameter("@Thucpham_thucanId", Thucpham_thucanId));
            DataTable table = new DataTable();
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;
        }
        public void TblTemp_Delete()
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TblTemp_Delete", cn);
            try
            {
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
        }
        public void TblTemp_DeleteByThucpham_thucanId(int Thucpham_thucanId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("sproc_TblTemp_deleteByThucpham_thucanId", cn);
            commad.Parameters.Add(new OleDbParameter("@Thucpham_thucanId", Thucpham_thucanId));
            try
            {
                commad.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
        }
    }
}
